<html>
<!-- =====================================================================

  File:      Generated file for Adventure Works Cycles Storefront Sample
  Summary:   Self-documentation for application
  Date:	     June 16, 2003

=====================================================================

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) 2003 Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= -->
<head>
  <link rel=stylesheet href=style.css>
</head>
<body>
<div class=SourcePanel style="font-size:12">
<pre style="background-color:white">
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Configuration</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span><span style="color: #4444FF">;</span>
<span style="color: #0000FF"><strong>using</strong></span> <span style="color: #2040a0">System</span>.<span style="color: #2040a0">Data</span>.<span style="color: #2040a0">SqlClient</span><span style="color: #4444FF">;</span>

<span style="color: #008000">/*=====================================================================

  File:      ReviewsDB.cs for Adventure Works Cycles Storefront Sample
  Summary:   Middle tier component for manipulating product reviews.
  Date:	     June 16, 2003

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */</span>

<span style="color: #0000FF"><strong>namespace</strong></span> <span style="color: #2040a0">Microsoft</span>.<span style="color: #2040a0">Samples</span>.<span style="color: #2040a0">SqlServer</span> 
<span style="color: #4444FF"><strong>{</strong></span>

    <span style="color: #008000">//*******************************************************</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// ReviewsDB Class</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">// Business/Data Logic Class that encapsulates all data</span>
    <span style="color: #008000">// logic necessary to list/access/add reviews from</span>
    <span style="color: #008000">// the AdventureWorks database.</span>
    <span style="color: #008000">//</span>
    <span style="color: #008000">//*******************************************************</span>

    <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>class</strong></span> <span style="color: #2040a0">ReviewsDB</span> <span style="color: #4444FF"><strong>{</strong></span>

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ReviewsDB.GetReviews() Method &lt;a name=&quot;GetReviews&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The GetReviews method returns a struct containing</span>
        <span style="color: #008000">// a forward-only, read-only DataReader.  This displays a list of all</span>
        <span style="color: #008000">// user-submitted reviews for a specified product.</span>
        <span style="color: #008000">// The SQLDataReaderResult struct also returns the SQL connection,</span>
        <span style="color: #008000">// which must be explicitly closed after the data from the DataReader</span>
        <span style="color: #008000">// is bound into the controls.</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ReviewsList.htm&quot; style=&quot;color:green&quot;&gt;usp_ReviewsList Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">GetReviews</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>int</strong></span> <span style="color: #2040a0">productID</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ReviewsList&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Add Parameters to SPROC</span>
            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterProductID</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ProductID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterProductID</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">productID</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterProductID</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Execute the command</span>
            <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlDataReader</span> <span style="color: #2040a0">result</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">ExecuteReader</span><span style="color: #4444FF">(</span><span style="color: #2040a0">CommandBehavior</span>.<span style="color: #2040a0">CloseConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Return the datareader result</span>
            <span style="color: #0000FF"><strong>return</strong></span> <span style="color: #2040a0">result</span><span style="color: #4444FF">;</span>
        <span style="color: #4444FF"><strong>}</strong></span>

        <span style="color: #008000">//*******************************************************</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// ReviewsDB.AddReview() Method &lt;a name=&quot;AddReview&quot;&gt;&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// The AddReview method adds a new review into the</span>
        <span style="color: #008000">// AdventureWorks database.</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">// Other relevant sources:</span>
        <span style="color: #008000">//     + &lt;a href=&quot;usp_ReviewsAdd.htm&quot; style=&quot;color:green&quot;&gt;usp_ReviewsAdd Stored Procedure&lt;/a&gt;</span>
        <span style="color: #008000">//</span>
        <span style="color: #008000">//*******************************************************</span>

        <span style="color: #0000FF"><strong>public</strong></span> <span style="color: #0000FF"><strong>void</strong></span> <span style="color: #2040a0">AddReview</span><span style="color: #4444FF">(</span><span style="color: #0000FF"><strong>int</strong></span> <span style="color: #2040a0">productID</span>, <span style="color: #2040a0">string</span> <span style="color: #2040a0">customerName</span>, <span style="color: #2040a0">string</span> <span style="color: #2040a0">customerEmail</span>, <span style="color: #0000FF"><strong>int</strong></span> <span style="color: #2040a0">rating</span>, <span style="color: #2040a0">string</span> <span style="color: #2040a0">comments</span><span style="color: #4444FF">)</span> <span style="color: #4444FF"><strong>{</strong></span>

            <span style="color: #008000">// Create Instance of Connection and Command Object</span>
            <span style="color: #2040a0">SqlConnection</span> <span style="color: #2040a0">myConnection</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlConnection</span><span style="color: #4444FF">(</span><span style="color: #2040a0">Properties</span>.<span style="color: #2040a0">Settings</span>.<span style="color: #2040a0">Default</span>.<span style="color: #2040a0">ConnectionString</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">SqlCommand</span> <span style="color: #2040a0">myCommand</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlCommand</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;usp_ReviewsAdd&quot;</span>, <span style="color: #2040a0">myConnection</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Mark the Command as a SPROC</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">CommandType</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">CommandType</span>.<span style="color: #2040a0">StoredProcedure</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Add Parameters to SPROC</span>
            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterProductID</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ProductID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterProductID</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">productID</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterProductID</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterCustomerName</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@CustomerName&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">50</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterCustomerName</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">customerName</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterCustomerName</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterEmail</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@CustomerEmail&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">50</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterEmail</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">customerEmail</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterEmail</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterRating</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Rating&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterRating</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">rating</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterRating</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterComments</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@Comments&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">NVarChar</span>, <span style="color: #FF0000">3850</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterComments</span>.<span style="color: #2040a0">Value</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">comments</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterComments</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #2040a0">SqlParameter</span> <span style="color: #2040a0">parameterReviewID</span> <span style="color: #4444FF">=</span> <span style="color: #0000FF"><strong>new</strong></span> <span style="color: #2040a0">SqlParameter</span><span style="color: #4444FF">(</span><span style="color: #444444">&quot;@ReviewID&quot;</span>, <span style="color: #2040a0">SqlDbType</span>.<span style="color: #2040a0">Int</span>, <span style="color: #FF0000">4</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">parameterReviewID</span>.<span style="color: #2040a0">Direction</span> <span style="color: #4444FF">=</span> <span style="color: #2040a0">ParameterDirection</span>.<span style="color: #2040a0">Output</span><span style="color: #4444FF">;</span>
            <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">Parameters</span>.<span style="color: #2040a0">Add</span><span style="color: #4444FF">(</span><span style="color: #2040a0">parameterReviewID</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>

            <span style="color: #008000">// Open the connection and execute the Command</span>
            <span style="color: #2040a0">try</span>
            <span style="color: #4444FF"><strong>{</strong></span>
                <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Open</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
                <span style="color: #2040a0">myCommand</span>.<span style="color: #2040a0">ExecuteNonQuery</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #4444FF"><strong>}</strong></span>
            <span style="color: #2040a0">finally</span>
            <span style="color: #4444FF"><strong>{</strong></span>
                <span style="color: #2040a0">myConnection</span>.<span style="color: #2040a0">Close</span><span style="color: #4444FF">(</span><span style="color: #4444FF">)</span><span style="color: #4444FF">;</span>
            <span style="color: #4444FF"><strong>}</strong></span>
        <span style="color: #4444FF"><strong>}</strong></span>
    <span style="color: #4444FF"><strong>}</strong></span>
<span style="color: #4444FF"><strong>}</strong></span>


</pre>
